# -*- coding: utf-8 -*-
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import os
import random
import re
import subprocess

import requests
from scrapy.pipelines.files import FilesPipeline
import scrapy
from BiliBiliSpider.spiders.userAgent import USER_AGENT
import pymysql
from BiliBiliSpider import settings

HEADERS = {
    'origin': 'https://www.bilibili.com',
}


class DownloadYkVideoPipeline:
    def process_item(self, item, spider):
        m3u8 = item["ykVideoM3u8"]
        videoName = item['ykVideoName']
        videoNum = item['ykNumber']
        # 判断是否存在该目录，若不存在则创建
        print("优酷视频名称和集数", videoName, videoNum)
        video_dirs = os.path.exists("videos/youku_video/{}".format(videoName))
        if not video_dirs:
            os.mkdir("videos/youku_video/{}".format(videoName))
        exist_name = os.listdir('"videos/youku_video/{}/".format(videoName)')
        video_name = '{}.mp4'.format(videoNum)
        if video_name in exist_name:
            print('优酷视频下载过', exist_name)
        else:
            cmd = f'E:/ffmpeg/ffmpeg/bin/ffmpeg.exe -i "{m3u8}" ' \
                f'-acodec copy -vcodec copy -absf aac_adtstoasc videos/youku_video/{videoName}/{videoNum}.mp4'
            print('cmds', cmd)
            subprocess.Popen(cmd, shell=True)
            print('优酷视频下载完成')
            return item


class YouKuSaveMysqlPipeline(object):
    """
    将爬取到的数据保存到数据库中
    """

    def __init__(self):
        self.connect = None
        self.cursor = None

    def open_spider(self, spider):
        # 连接MySQL数据库
        self.connect = pymysql.connect(
            host=settings.HOST,
            db=settings.YK_DATABASE,
            user=settings.USER,
            password=settings.PASSWORD,
            charset='utf8',
        )

        # 创建游标对象
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        """
        向数据库中插入数据
        :param item:
        :param spider:
        :return:
        """
        # 提取动漫集数，为1、2、3...
        video_path = 'videos/youku_video/{}/{}.mp4'.format(item['ykVideoName'], item['ykNumber'])  # 视频播放链接
        video_info_sql = "insert into yk_video_info(video_name, video_image_link) value(%s, %s);"
        video_number_sql = "insert into yk_video_number(video_name, video_num, video_number_link) value(%s, %s, %s);"
        # 查询video_info表中数据
        select_repetition = 'select video_name from yk_video_info'
        try:
            self.cursor.execute(select_repetition)
        except Exception as e:
            print("数据库查询失败", e)
        # 将查询到的值提取出来
        select_repetition_data = self.cursor.fetchall()
        print("sql", select_repetition_data)
        # 判断动漫名字是否已经存在数据库video_info表中, 若不存在表中则插入该数据 [(video_name,), (video_name1,)]
        if (item['ykVideoName'],) not in select_repetition_data:
            try:
                self.cursor.execute(
                    video_info_sql,
                    (item['ykVideoName'], item['ykImageLink'])
                )
            except Exception as e:
                print("插入video_info表出错", e)
        try:
            self.cursor.execute(
                video_number_sql,
                (item['ykVideoName'], item['ykNumber'], video_path)
            )
        except Exception as e:
            print("插入video_number表出错", e)
        # 提交数据
        self.connect.commit()
        return item

    def close_spider(self, spider):
        self.cursor.close()  # 关闭游标
        self.connect.close()  # 关闭连接


class DownloadTxVideoPipeline:
    def process_item(self, item, spider):
        m3u8 = item["videoM3u8"]
        print('videoM3u8', m3u8)
        videoName = item['videoName']
        videoNum = item['txNumber']
        print("视频名称和集数", videoName, videoNum)
        video_dirs = os.path.exists("videos/tencent_video/{}".format(videoName))
        if not video_dirs:
            os.mkdir("videos/tencent_video/{}".format(videoName))
        exist_name = os.listdir("videos/tencent_video/{}/".format(videoName))
        video_name = '{}.mp4'.format(videoNum)
        if video_name in exist_name:
            print('已下载这个视频', exist_name)
        else:
            cmd = f'E:/ffmpeg/ffmpeg/bin/ffmpeg.exe -i {m3u8} ' \
                f'-acodec copy -vcodec copy -absf aac_adtstoasc videos/tencent_video/{videoName}/{videoNum}.mp4'
            subprocess.Popen(cmd, shell=True)
            print('腾讯视频下载完成', cmd)
            return item


class TencentSaveMysqlPipeline(object):
    """
    将爬取到的数据保存到数据库中
    """

    def __init__(self):
        self.connect = None
        self.cursor = None

    def open_spider(self, spider):
        # 连接MySQL数据库
        self.connect = pymysql.connect(
            host=settings.HOST,
            db=settings.TX_DATABASE,
            user=settings.USER,
            password=settings.PASSWORD,
            charset='utf8',
        )

        # 创建游标对象
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        """
        向数据库中插入数据
        :param item:
        :param spider:
        :return:
        """
        # 提取动漫集数，为1、2、3...
        video_path = 'videos/tencent_video/{}/{}.mp4'.format(item['videoName'], item['txNumber'])  # 视频播放链接
        video_info_sql = "insert into tx_video_info(video_name, video_image_link) value(%s, %s);"
        video_number_sql = "insert into tx_video_number(video_name, video_num, video_number_link) value(%s, %s, %s);"
        # 查询video_info表中数据
        select_repetition = 'select video_name from tx_video_info'
        try:
            self.cursor.execute(select_repetition)
        except Exception as e:
            print("数据库查询失败", e)
        # 将查询到的值提取出来
        select_repetition_data = self.cursor.fetchall()
        print("sql", select_repetition_data)
        # 判断动漫名字是否已经存在数据库video_info表中, 若不存在表中则插入该数据 [(video_name,), (video_name1,)]
        if (item['videoName'],) not in select_repetition_data:
            try:
                self.cursor.execute(
                    video_info_sql,
                    (item['videoName'], item['txVideoImage'])
                )
            except Exception as e:
                print("插入video_info表出错", e)
        try:
            self.cursor.execute(
                video_number_sql,
                (item['videoName'], item['txNumber'], video_path)
            )
        except Exception as e:
            print("插入video_number表出错", e)
        # 提交数据
        self.connect.commit()
        return item

    def close_spider(self, spider):
        self.cursor.close()  # 关闭游标
        self.connect.close()  # 关闭连接


class DownloadVideoPipeline:
    """
    视频下载管道,需要在settings.py有存储变量，不然不能爬取
    """

    def process_item(self, item, spider):
        # 文件下载路径
        HEADERS['Referer'] = item['anime_link']
        HEADERS['User-Agent'] = random.choice(USER_AGENT)
        if '连载' in item['anime_update_status']:
            # 获取已经下载过的视频名称
            exist_name_serial = os.listdir('videos/anime_serial/{}/'.format(item['anime_name']))
            video_name_serial = '{}.mp4'.format(item['anime_num_name'])
            print('看一哈', exist_name_serial, video_name_serial)
            if video_name_serial in exist_name_serial:
                print('视频已下载', exist_name_serial)
            else:
                res = requests.get(url=item['download_video'], headers=HEADERS)
                print("视频状态", res.status_code, type(res.status_code), item)
                if res.status_code == 200:
                    with open('videos/anime_serial/{}/{}.mp4'.format(item['anime_name'], item['anime_num_name']), 'wb') as f:
                        f.write(res.content)
                    print("视频完成")
                    # 注意return item，若不return item则下面的管道无法获取到item的值
                    return item
        elif '完结' in item['anime_update_status']:
            exist_name_end = os.listdir('videos/anime_end/{}/'.format(item['anime_name']))
            video_name_end = '{}.mp4'.format(item['anime_num_name'])
            print('看一哈', exist_name_end, video_name_end)
            if video_name_end in exist_name_end:
                print('视频已下载', exist_name_end)
            else:
                res = requests.get(url=item['download_video'], headers=HEADERS)
                print("视频状态", res.status_code, type(res.status_code), item)
                if res.status_code == 200:
                    with open('videos/anime_end/{}/{}.mp4'.format(item['anime_name'], item['anime_num_name']), 'wb') as f:
                        f.write(res.content)
                    print("视频完成")
                    return item


class DownloadAudioPipeline:
    """
    音频下载管道
    """

    def process_item(self, item, spider):
        HEADERS['User-Agent'] = random.choice(USER_AGENT)
        if '连载' in item['anime_update_status']:
            exist_name_serial = os.listdir('videos/anime_serial/{}/'.format(item['anime_name']))
            video_name_serial = '{}.mp3'.format(item['anime_num_name'])
            if video_name_serial in exist_name_serial:
                print('音频已下载', exist_name_serial)
            else:
                res = requests.get(url=item['download_audio'], headers=HEADERS)
                print("音频状态", res.status_code, type(res.status_code))
                if res.status_code == 200:
                    with open('videos/anime_serial/{}/{}.mp3'.format(item['anime_name'], item['anime_num_name']), 'wb') as f:
                        f.write(res.content)
                    print("音乐完成")
                    return item
        elif '完结' in item['anime_update_status']:
            exist_name_end = os.listdir('videos/anime_end/{}/'.format(item['anime_name']))
            video_name_end = '{}.mp3'.format(item['anime_num_name'])
            if video_name_end in exist_name_end:
                print('音频已下载', exist_name_end)
            else:
                res = requests.get(url=item['download_audio'], headers=HEADERS)
                print("音频状态", res.status_code, type(res.status_code))
                if res.status_code == 200:
                    with open('videos/anime_end/{}/{}.mp3'.format(item['anime_name'], item['anime_num_name']), 'wb') as f:
                        f.write(res.content)
                    print("音乐完成")
                    return item


class MergeVideoPipeline(object):
    def process_item(self, item, spider):
        if '连载' in item['anime_update_status']:
            self.merge_videos(item['anime_name'], item['anime_num_name'], 'anime_serial')
        elif '完结' in item['anime_update_status']:
            self.merge_videos(item['anime_name'], item['anime_num_name'], 'anime_end')
        return item

    def merge_videos(self, anime_name, anime_num_name, status_path):
        """
        视频合成函数
        :param anime_name: 动漫名
        :param anime_num_name: 动漫集数名
        :return:
        """
        # 这一段是视频合成，可以提取成函数
        try:
            command = f"E:/ffmpeg/ffmpeg/bin/ffmpeg.exe -i videos/{status_path}/{anime_name}/{anime_num_name}.mp4 -i" \
                      + f" videos/{status_path}/{anime_name}/{anime_num_name}.mp3 -c:v copy -c:a aac " \
                      + f"-strict experimental merge_audios_videos/{status_path}/{anime_name}/{anime_num_name}.mp4"
            subprocess.Popen(command, shell=True)
            print("合并成功", os.getcwd())
        except Exception as e:
            print("合并失败", e)


# 图片可以不下载直接存数据库，然后加载出来
# 数据库名称spider_video
class SaveMysqlPipeline(object):
    """
    将爬取到的数据保存到数据库中
    """

    def __init__(self):
        self.connect = None
        self.cursor = None

    def open_spider(self, spider):
        # 连接MySQL数据库
        self.connect = pymysql.connect(
            host=settings.HOST,
            db=settings.DATABASE,
            user=settings.USER,
            password=settings.PASSWORD,
            charset='utf8',
        )

        # 创建游标对象
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        """
        向数据库中插入数据
        :param item:
        :param spider:
        :return:
        """
        if '连载' in item['anime_update_status']:
            anime_number_link = 'merge_audios_videos/anime_serial/{}/{}.mp4'.format(item['anime_name'],
                                                                                    item['anime_num_name'])
            item['anime_update_status'] = '连载'
        elif '完结' in item['anime_update_status']:
            anime_number_link = 'merge_audios_videos/anime_end/{}/{}.mp4'.format(item['anime_name'],
                                                                                 item['anime_num_name'])
            item['anime_update_status'] = '完结'
        # 提取动漫集数，为1、2、3...
        anime_num_name = re.findall(r"\d+", item['anime_num_name'], re.S)[-1]
        video_info_sql = "insert into video_info(anime_name, anime_update, anime_image) value(%s, %s, %s);"
        video_number_sql = "insert into video_number(anime_name, anime_number, number_link) value(%s, %s, %s);"
        # 查询video_info表中数据
        select_repetition = 'select anime_name from video_info'
        try:
            self.cursor.execute(select_repetition)
        except Exception as e:
            print("数据库查询失败", e)
        # 将查询到的值提取出来
        select_repetition_data = self.cursor.fetchall()
        print("sql", select_repetition_data)
        # 判断动漫名字是否已经存在数据库video_info表中, 若不存在表中则插入该数据
        if (item['anime_name'],) not in select_repetition_data:
            try:
                self.cursor.execute(
                    video_info_sql,
                    (item['anime_name'], item['anime_update_status'], item['anime_image'])
                )
            except Exception as e:
                print("插入video_info表出错", e)
        try:
            self.cursor.execute(
                video_number_sql,
                (item['anime_name'], anime_num_name, anime_number_link)
            )
        except Exception as e:
            print("插入video_number表出错", e)
        # 提交数据
        self.connect.commit()
        return item

    def close_spider(self, spider):
        self.cursor.close()  # 关闭游标
        self.connect.close()  # 关闭连接
